//
//  GDDataBase.m
//  Gunterapple
//
//  Created by 热带雨林 on 2021/1/29.
//  Copyright © 2021 热带雨林. All rights reserved.
//

#import "GDDataBase.h"
/**
 全局静态数据库变量 仅限当前文件可用
 */
static FMDatabase *db = nil;

/**
 防反编译
 */
static inline NSString *DocumentPath(){
    return NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
}
//static NSString *const

@implementation GDDataBase

static GDDataBase *_instance;

/**
创建数据库
 */

+ (void)creatSqliteWithSqliteName:(NSString *)sqliteName{
    NSString *dbPath = [DocumentPath() stringByAppendingPathComponent:[NSString stringWithFormat:@"%@.db",SqliteName]];
    NSFileManager *fileManger = [NSFileManager defaultManager];
    BOOL result = [fileManger fileExistsAtPath:dbPath];
    db = [FMDatabase databaseWithPath:dbPath];
    //DB path 路径
    NSLog(@"%@",dbPath);
    if(result){
        NSLog(@"已经创建过该数据库了");
    }else{
        NSLog(@"创建数据库成功！！！");
    }
}

/**
创建表
 */
+ (void)creatTableName:(NSString *)tableName withTableStatusBlock:(void(^)(BOOL status))statusBlock{
    //创建表之前需要先打开数据库
    [GDDataBase openSqlitestatusBlock:^(BOOL status) {
        if(status == YES){
            //先判断创建的表是否已经存在了
            FMResultSet *rs = [db executeQuery:@"select count(*) as 'count' from sqlite_master where type ='table' and name = ?",tableName];
            while ([rs next]) {
                NSInteger count = [rs intForColumn:@"count"];
                if(0 == count){
                    //没有创建这个表
                    statusBlock(NO);
                    //如果打开数据库成功，就开始创建表格，ID是搜索关键字
                    NSString *tableNameString = [NSString stringWithFormat:@"create table if not exists %@('ID' INTEGER PRIMARY KEY AUTOINCREMENT , 'descStr' TEXT NOT NULL, 'tipStr' TEXT NOT NULL)",tableName];
                    //判断是否创建成功
                    BOOL result = [db executeUpdate:tableNameString];
                    if(result){
                        NSLog(@"创建表成功！");
                    }else{
                        NSLog(@"创建表失败！");
                    }
                    //创建表成功之后，数据库就关闭
                    [GDDataBase closeSqlitestatusBlock:^(BOOL status) {
                        if(status == YES){
                            NSLog(@"关闭成功！");
                        }else{
                            NSLog(@"关闭失败！");
                        }
                    }];
                }else{
                    //已经创建了表 就不需要创建了
                    statusBlock(YES);
                }
            }
        }else{
            NSLog(@"打开失败！");
        }
    }];
}

/**
 插入单条数据
 */
+(void)addSqliteID:(NSString *)ID withName:(NSString *)tipStr withdescName:(NSString *)descStr withTableName:(NSString *)tableName success:(void(^)(BOOL status))success fail:(void(^)(BOOL status))fail{
    [GDDataBase openSqlitestatusBlock:^(BOOL status) {
        if(status == YES){
            
            /**
             数据量过小没有必要
             */
//            [db beginTransaction];
            BOOL isRollBack = NO;
            @try {
                NSString *sql = [NSString stringWithFormat:@"insert into '%@'(ID,descStr,tipStr) values(?,?,?)",tableName];
                BOOL result = [db executeUpdate:sql withArgumentsInArray:@[ID,descStr,tipStr]];
                if(result){
                    success(YES);
                }else{
                    fail(NO);
                }
            } @catch (NSException *exception) {
                isRollBack = YES;
                [db rollback];
            } @finally {
                if(!isRollBack){
//                    [db commit];
                }
            }
            [GDDataBase closeSqlitestatusBlock:^(BOOL status) {
                if(status == YES){
                    NSLog(@"数据库关闭成功！");
                }else{
                    NSLog(@"数据库关闭失败！");
                }
            }];
        }else{
            NSLog(@"数据库打开失败！");
        }
    }];
}

/**
插入多条数据
 */
+(void)addSqliteTableName:(NSString *)tableName modelArray:(NSArray *)modelArray failArray:(nonnull void(^)(NSArray *_Nonnull))failArray{
    NSMutableArray *failArrays = [@[]mutableCopy];
    [GDDataBase openSqlitestatusBlock:^(BOOL status) {
        if(status){
            //开始处理事务
            [db beginTransaction];
            //是否回滚
            BOOL isRollBack = NO;
            @try {
                for (int i = 0; i < modelArray.count; i ++) {
                    BOOL result = [db executeUpdate:[NSString stringWithFormat:@"insert into '%@'(ID,descStr,tipStr) values(?,?,?)", tableName] withArgumentsInArray:@[modelArray[i][@"ID"], modelArray[i][@"descStr"], modelArray[i][@"tipStr"]]];
                    if(result){
                        NSLog(@"插入成功!");
                    }else{
                        [failArrays addObject:modelArray[i][@"ID"]];
                        NSLog(@"插入失败！");
                    }
                }
                failArray(failArrays);
            } @catch (NSException *exception) {
                isRollBack = YES;
                [db rollback];
            } @finally {
                if(!isRollBack){
                    [db commit];
                }
            }
            [GDDataBase closeSqlitestatusBlock:^(BOOL status) {
                if(status){
                    NSLog(@"数据库关闭成功！");
                }else{
                    NSLog(@"数据库关闭失败！");
                }
            }];
             NSLog(@"打开数据库成功！");
        }else{
            NSLog(@"打开数据库失败！");
        }
    }];
}
/**
 查询单条数据
 */
+ (void)selectSqliteWithTableName:(NSString *)tableName withSqliteID:(NSString *)ID success:(void(^)(NSDictionary * dic))success fail:(void(^)(BOOL status))fail{
    [GDDataBase openSqlitestatusBlock:^(BOOL status) {
        if(status == YES){
//            [db beginTransaction];
            BOOL isRollBack = NO;
            @try {
                NSString *sql = [NSString stringWithFormat:@"select * from %@ where ID = ?",tableName];
                FMResultSet *result = [db executeQuery:sql withArgumentsInArray:@[ID]];
                if([result next]){
                    NSString *ID = [result stringForColumn:@"ID"];
                    NSString *descStr = [result stringForColumn:@"descStr"];
                    NSString *tipStr = [result stringForColumn:@"tipStr"];
                    NSMutableDictionary *dic = [@{}mutableCopy];
                    [dic setValue:ID forKey:@"ID"];
                    [dic setValue:descStr forKey:@"descStr"];
                    [dic setValue:tipStr forKey:@"tipStr"];
                    success(dic);
                }else if(![result next]){
                    fail(NO);
                }
            } @catch (NSException *exception) {
                isRollBack = YES;
                [db rollback];
            } @finally {
                if(!isRollBack){
//                    [db commit];
                }
            }
            [GDDataBase closeSqlitestatusBlock:^(BOOL status) {
                if(status == YES){
                    NSLog(@"数据库关闭成功！");
                }else{
                    NSLog(@"数据库关闭失败！");
                }
            }];
            NSLog(@"数据库打开成功！");
        }else{
            NSLog(@"数据库打开失败！");
        }
    }];
}

/**
 查询多条数据
 */
+ (void)selectArraySqliteWithTableName:(NSString *)tableName withSqliteArray:(NSArray *)sqliteArray success:(void(^)(NSArray *array,NSArray *notSelectArray))success fail:(void(^)(BOOL status))fail{
    [GDDataBase openSqlitestatusBlock:^(BOOL status) {
        if(status == YES){
            /**
             查询多条，批量查询  开启事务
             */
            [db beginTransaction];
            BOOL isRollBack = NO;
            @try {
                NSMutableArray *array = [@[]mutableCopy];
                NSMutableArray *notSelectArray = [@[]mutableCopy];
                for (int i = 0; i < sqliteArray.count; i ++) {
                    NSString *sql = [NSString stringWithFormat:@"select * from %@ where ID = ?",tableName];
                    FMResultSet *result = [db executeQuery:sql withArgumentsInArray:@[sqliteArray[i]]];
                    if([result next]){
                        NSString *ID = [result stringForColumn:@"ID"];
                        NSString *descStr = [result stringForColumn:@"descStr"];
                        NSString *tipStr = [result stringForColumn:@"tipStr"];
                        NSMutableDictionary *dic = [@{}mutableCopy];
                        [dic setValue:ID forKey:@"ID"];
                        [dic setValue:descStr forKey:@"descStr"];
                        [dic setValue:tipStr forKey:@"tipStr"];
                        [array addObject:dic];
                    }else{
                        [notSelectArray addObject:sqliteArray[i]];
                    }
                }
                success(array,notSelectArray);
            } @catch (NSException *exception) {
                isRollBack = YES;
                [db rollback];
            } @finally {
                if(!isRollBack){
                    [db commit];
                }
            }
            [GDDataBase closeSqlitestatusBlock:^(BOOL status) {
                if(status == YES){
                    NSLog(@"关闭数据库成功");
                }else{
                    NSLog(@"关闭数据库失败");
                }
            }];
            NSLog(@"打开成功了");
        }else{
           NSLog(@"打开失败了");
        }
    }];
}

/**
 查询表中所有数据
 */
+(void)selectSqliteAllModelWithTable:(NSString *)tableName success:(void(^)(NSArray *array))success fail:(void(^)(BOOL fail))fail{
    [GDDataBase openSqlitestatusBlock:^(BOOL status) {
        if(status){
            [db beginTransaction];
            BOOL isRollBack = NO;
            @try {
                NSMutableArray *array = [@[]mutableCopy];
                FMResultSet *set = [db executeQuery:[NSString stringWithFormat: @"select * from '%@'",tableName]];
                while ([set next]) {
                    NSMutableDictionary * dic = [@{}mutableCopy];
                    [dic setValue:[set stringForColumn:@"ID"] forKey:@"ID"];
                    [dic setValue:[set stringForColumn:@"tipStr"] forKey:@"tipStr"];
                    [dic setValue:[set stringForColumn:@"descStr"] forKey:@"descStr"];
                    [array addObject:dic];
                }
                success(array);
            } @catch (NSException *exception) {
                isRollBack = YES;
                [db rollback];
            } @finally {
                if(!isRollBack){
                    [db commit];
                }
            }
            [GDDataBase closeSqlitestatusBlock:^(BOOL status) {
                if(status){
                    NSLog(@"关闭数据库成功");
                }else{
                    NSLog(@"关闭数据库失败");
                }
            }];
        }else{
            NSLog(@"没有数据！");
        }
    }];
}

/**
 删除数据库中表的某一条数据
 */
+(void)deleteSqliteWithTableName:(NSString *)tableName withSqliteID:(NSString *)ID success:(void(^)(BOOL status))success fail:(void(^)(BOOL status))fail{
    [GDDataBase openSqlitestatusBlock:^(BOOL status) {
        if(status == YES){
            [db beginTransaction];
            BOOL isRollBack = NO;
            @try {
                NSString *sql = [NSString stringWithFormat:@"delete from %@ where ID = ?",tableName];
                BOOL result = [db executeUpdate:sql withArgumentsInArray:@[(ID)]];
                if(result){
                    success(YES);
                }else{
                    fail(NO);
                }
                NSLog(@"数据库打开成功!");
            } @catch (NSException *exception) {
                isRollBack = YES;
                [db rollback];
            } @finally {
                if(!isRollBack){
                    [db commit];
                }
            }
            [GDDataBase closeSqlitestatusBlock:^(BOOL status) {
                if(status == YES){
                    NSLog(@"数据库关闭成功");
                }else{
                    NSLog(@"数据库关闭失败");
                }
            }];
        }else{
           NSLog(@"数据库打开失败");
        }
    }];
}

/**
 删除数据库中表的多条数据
 */
+(void)deletaSqliteArrayWithTableName:(NSString *)tableName withSqliteArray:(NSArray *)array success:(void(^)(BOOL status))success fail:(void(^)(NSArray *failArray))fail{
    [GDDataBase openSqlitestatusBlock:^(BOOL status) {
        if(status == YES){
            [db beginTransaction];
            BOOL isRollBack = NO;
            @try {
                NSString *sql = [NSString stringWithFormat:@"delete from %@ where ID = ?",tableName];
                NSMutableArray *failArray = [@[]mutableCopy];
                for (int i = 0; i <array.count; i ++) {
                    BOOL result = [db executeUpdate:sql withArgumentsInArray:@[array[i]]];
                    if(result){
                        NSLog(@"删除成功");
                    }else{
                        NSLog(@"删除失败");
                        [failArray addObject:array[i]];
                    }
                }
                if(failArray.count == 0){
                    success(YES);
                }else{
                    fail(array);
                }
                NSLog(@"打开数据库成功");
            } @catch (NSException *exception) {
                isRollBack = YES;
                [db rollback];
            } @finally {
                if(!isRollBack){
                    [db commit];
                }
            }
            [GDDataBase closeSqlitestatusBlock:^(BOOL status) {
                if(status == YES){
                    NSLog(@"关闭数据库成功");
                }else{
                    NSLog(@"关闭数据库失败");
                }
            }];
        }else{
           NSLog(@"打开数据库失败");
        }
    }];
}
/**
 删除数据库中表的所有数据
 */
+ (void)deleteSqliteAllDataWithTableName:(NSString *)tableName success:(void(^)(BOOL status))success fail:(void(^)(NSArray *array))fail{
    [GDDataBase openSqlitestatusBlock:^(BOOL status) {
        if(status == YES){
            [db beginTransaction];
            BOOL isRollBack = NO;
            @try {
                NSString *sql = [NSString stringWithFormat:@"delete from %@",tableName];
                BOOL result = [db executeUpdate:sql];
                if(result){
                    NSLog(@"删除所有数据成功！");
                    success(YES);
                }else{
                    NSLog(@"删除所有数据成功！");
                }
            } @catch (NSException *exception) {
                isRollBack = YES;
                [db rollback];
            } @finally {
                if(!isRollBack){
                    [db commit];
                }
            }
            [GDDataBase closeSqlitestatusBlock:^(BOOL status) {
                if(status == YES){
                    NSLog(@"关闭数据库成功");
                }else{
                    NSLog(@"关闭数据库失败");
                }
            }];
            NSLog(@"打开数据库成功");
        }else{
            NSLog(@"打开数据库失败");
        }
    }];
}

/**
 修改数据库中单条数据
 */
+ (void)updataSqliteWithTableName:(NSString *)tableName withID:(NSString *)ID withTipStr:(NSString *)tipStr withDescStr:(NSString *)descStr success:(void(^)(BOOL status))success fail:(void(^)(BOOL fail))fail{
    [GDDataBase openSqlitestatusBlock:^(BOOL status) {
        if(status == YES){
            [db beginTransaction];
            BOOL isRollBack = NO;
            @try {
                NSString *sql = [NSString stringWithFormat:@"update %@ set tipStr = ?, descStr = ? where ID = ?",tableName];
                BOOL result = [db executeUpdate:sql withArgumentsInArray:@[tipStr,descStr,ID]];
                if(result){
                    success(YES);
                }else{
                    fail(NO);
                }
            } @catch (NSException *exception) {
                isRollBack = YES;
                [db rollback];
            } @finally {
                if(!isRollBack){
                    [db commit];
                }
            }
            [GDDataBase closeSqlitestatusBlock:^(BOOL status) {
                if(status == YES){
                     NSLog(@"关闭数据库成功");
                }else{
                     NSLog(@"关闭数据库失败");
                }
            }];
            NSLog(@"打开数据库成功");
        }else{
            NSLog(@"打开数据库失败");
        }
    }];
}
/**
 修改数据库中多条数据
 */
+ (void)updateSqlieWithTableName:(NSString *)tableName withArray:(NSArray *)array success:(void(^)(BOOL status))success fail:(void(^)(NSArray *failArray))failArray{
    NSMutableArray *notUpdateArray = [NSMutableArray array];
    [GDDataBase openSqlitestatusBlock:^(BOOL status) {
        if(status == YES){
            [db beginTransaction];
            BOOL isRollBack = NO;
            @try {
                for(int i = 0; i < array.count; i ++){
                    NSString *sql = [NSString stringWithFormat:@"update %@ set tipStr = ? , descStr = ? where ID = ?",tableName];
                    BOOL result = [db executeUpdate:sql withArgumentsInArray:@[array[i][@"tipStr"],array[i][@"descStr"],array[i][@"ID"]]];
                    if(result){
                        NSLog(@"修改多条数据成功！");
                    }else{
                        [notUpdateArray addObject:array[i]];
                    }
                }
                if(notUpdateArray.count == 0){
                    success(YES);
                }else{
                    failArray(notUpdateArray);
                }
            } @catch (NSException *exception) {
                isRollBack = YES;
                [db rollback];
            } @finally {
                if(!isRollBack){
                    [db commit];
                }
            }
            [GDDataBase closeSqlitestatusBlock:^(BOOL status) {
                if(status == YES){
                    NSLog(@"关闭数据库成功");
                }else{
                    NSLog(@"关闭数据库失败");
                }
            }];
            NSLog(@"打开数据库成功");
        }else{
            NSLog(@"打开数据库失败");
        }
    }];
}
/**
 删除数据库中的表  使用此命令时要特别注意，因为一旦一个表被删除，表中所有信息也将永远丢失。
 */
+(void)dropTableWithTableName:(NSString *)tableName withTableStatus:(void(^)(BOOL status))statusBlock{
    [GDDataBase openSqlitestatusBlock:^(BOOL status) {
        if(status){
            [db beginTransaction];
            //DROP TABLE IF EXISTS creNO
            NSString *sql = [NSString stringWithFormat:@"DROP TABLE IF EXISTS %@",tableName];
            BOOL result = [db executeUpdate:sql];
            if(result){
                statusBlock(YES);
            }else{
                statusBlock(NO);
            }
            //注意，开启数据库使用完毕之后，需要将其关闭
            [GDDataBase closeSqlitestatusBlock:^(BOOL status) {
                if (status) {
                    NSLog(@"关闭成功");
                }else{
                    NSLog(@"关闭失败");
                }
            }];
        }else{
             
        }
    }];
}
/**
 打开数据库
 */
+(BOOL)openSqlitestatusBlock:(void(^)(BOOL status))statusBlock{
    [db open];
    if([db open]){
        NSLog(@"数据库打开成功！");
        statusBlock(YES);
        return YES;
    }else{
        NSLog(@"数据库打开失败！");
        statusBlock(NO);
        return NO;
    }
}
/**
关闭数据库
 */
+(BOOL)closeSqlitestatusBlock:(void(^)(BOOL status))statusBlock{
    [db close];
    if([db close]){
        NSLog(@"数据库关闭成功!");
        statusBlock(YES);
        return YES;
    }else{
        NSLog(@"数据库关闭失败！");
        statusBlock(NO);
        return NO;
    }
}
@end
